﻿//Please add references


using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using YwangHRMSys.DBUtility;


namespace YwangHRMSys.DAL
    {
        /// <summary>
        ///     数据访问类:EmployeeInfoDAL
        /// </summary>
        public class EmployeeInfoDAL
            {
                #region 基础方法

                #region 开始生成tomodel提高代码的重用性

                /// 开始生成tomodel提高代码的重用性
                private EmployeeInfoModel ToModel( DataRow row )
                {
                    var model = new EmployeeInfoModel();
                    model.Id = (Guid) SqlHelper.FromDbValue(row["Id"]);
                    model.FileNo = (String) SqlHelper.FromDbValue(row["FileNo"]);
                    model.Name = (String) SqlHelper.FromDbValue(row["Name"]);
                    model.DepartmentId = (Guid) SqlHelper.FromDbValue(row["DepartmentId"]);
                    model.JobTitleId = (Guid)SqlHelper.FromDbValue(row["JobTitleId"]);
                    model.Gender = (Guid) SqlHelper.FromDbValue(row["Gender"]);
                    model.Post = (Guid) SqlHelper.FromDbValue(row["Post"]);
                    model.IdCard = (String) SqlHelper.FromDbValue(row["IdCard"]);
                    model.DateOfBirth = (DateTime) SqlHelper.FromDbValue(row["DateOfBirth"]);
                    model.DomicileAddressId = (Guid) SqlHelper.FromDbValue(row["DomicileAddressId"]);
                    model.CurrentAddressId = (Guid) SqlHelper.FromDbValue(row["CurrentAddressId"]);
                    model.Age = (String) SqlHelper.FromDbValue(row["Age"]);
                    model.EducationId = (Guid) SqlHelper.FromDbValue(row["EducationId"]);
                    model.PoliticalAffiliationId = (Guid) SqlHelper.FromDbValue(row["PoliticalAffiliationId"]);
                    model.ContractId = (Guid?) SqlHelper.FromDbValue(row["ContractId"]);
                    model.TrailDaysId = (Guid) SqlHelper.FromDbValue(row["TrailDaysId"]);
                    model.Nationalities = (String) SqlHelper.FromDbValue(row["Nationalities"]);
                    model.MaritalStatusId = (Guid) SqlHelper.FromDbValue(row["MaritalStatusId"]);
                    model.Email = (String) SqlHelper.FromDbValue(row["Email"]);
                    model.TelePhone = (String) SqlHelper.FromDbValue(row["TelePhone"]);
                    model.EmergencyContact = (String) SqlHelper.FromDbValue(row["EmergencyContact"]);
                    model.EmergencyContactPhone = (String) SqlHelper.FromDbValue(row["EmergencyContactPhone"]);
                    model.Remark = (String) SqlHelper.FromDbValue(row["Remark"]);
                    model.IsDimission = (Boolean) SqlHelper.FromDbValue(row["IsDimission"]);
                    return model;
                } //tomodel的结束。

                #endregion

                #region listAll开始

                //查询所有EmployeeInfo的数据列表
                public IEnumerable <EmployeeInfoModel> ListAll()
                {
                    var list = new List <EmployeeInfoModel>();
                    DataTable table = SqlHelper.ExecuteDataTable
                            (
                                    "Select Id,FileNo,Name,DepartmentId,JobTitleId,Gender,Post,IdCard,DateOfBirth,DomicileAddressId,CurrentAddressId,Age,EducationId,PoliticalAffiliationId,ContractId,TrailDaysId,Nationalities,MaritalStatusId,Email,TelePhone,EmergencyContact,EmergencyContactPhone,Remark,IsDimission FROM  EmployeeInfo where IsDimission=0 ");
                    foreach (DataRow row in table.Rows)
                        {
                            EmployeeInfoModel model = ToModel(row);
                            list.Add(model);
                        }
                    return list;
                } //listAllEmployeeInfo结束

                #endregion

                public EmployeeInfoModel GetById(Guid id)
                {
                    DataTable table =
                        SqlHelper.ExecuteDataTable(
                            "SELECT Id,FileNo,Name,DepartmentId,JobTitleId,Gender,Post,IdCard,DateOfBirth,DomicileAddressId,CurrentAddressId,Age,EducationId,PoliticalAffiliationId,ContractId,TrailDaysId,Nationalities,MaritalStatusId,Email,TelePhone,EmergencyContact,EmergencyContactPhone,Remark,IsDimission FROM  EmployeeInfo  WHERE  Id = @Id",
                            new SqlParameter("@Id",
                                             id));

                    if (table.Rows.Count <= 0)
                    {
                        return null;
                    }
                    if (table.Rows.Count == 1)
                        {
                            return ToModel(table.Rows[0]);
                        }
                    throw new Exception();
                }


                #region  根据ID来查询EmployeeInfo的信息

                public EmployeeInfoModel[] GetByEmployeeInfoId( Guid Id )
                {
                    DataTable table = SqlHelper.ExecuteDataTable
                            (
                                    " SELECT Id,FileNo,Name,DepartmentId,JobTitleId,Gender,Post,IdCard,DateOfBirth,DomicileAddressId,CurrentAddressId,Age,EducationId,PoliticalAffiliationId,ContractId,TrailDaysId,Nationalities,MaritalStatusId,Email,TelePhone,EmergencyContact,EmergencyContactPhone,Remark,IsDimission FROM  EmployeeInfo  WHERE  Id = @Id",
                                    new SqlParameter("@Id", Id));
                    if (table.Rows.Count <= 0)
                        {
                            return null;
                        }
                    if (table.Rows.Count == 1)
                        {
                            return ToEmployeeInfoModels(table);

                        }
                    throw new Exception();
                }

                #endregion

                #region  根据ID修改EmployeeInfo的信息

                public void UpdateEmployeeInfo( EmployeeInfoModel model )
                {
                    SqlHelper.ExecuteNonQuery
                            (
                                    "  UPDATE  EmployeeInfo SET  Id = @Id,FileNo = @FileNo,Name = @Name,DepartmentId = @DepartmentId,JobTitleId = @JobTitleId,Gender = @Gender,Post = @Post,IdCard = @IdCard,DateOfBirth = @DateOfBirth,Age = @Age,EducationId = @EducationId,PoliticalAffiliationId = @PoliticalAffiliationId,TrailDaysId = @TrailDaysId,Nationalities = @Nationalities,MaritalStatusId = @MaritalStatusId,Email = @Email,TelePhone = @TelePhone,EmergencyContact = @EmergencyContact,EmergencyContactPhone = @EmergencyContactPhone,Remark = @Remark,IsDimission = @IsDimission  WHERE  Id = @Id",
                                    new SqlParameter("@Id", model.Id),
                                    new SqlParameter("@FileNo", model.FileNo),
                                    new SqlParameter("@Name", model.Name),
                                    new SqlParameter("@DepartmentId", model.DepartmentId),
                                    new SqlParameter("@JobTitleId", model.JobTitleId),
                                    new SqlParameter("@Gender", model.Gender),
                                    new SqlParameter("@Post", model.Post),
                                    new SqlParameter("@IdCard", model.IdCard),
                                    new SqlParameter("@DateOfBirth", model.DateOfBirth),
                                    new SqlParameter("@Age", model.Age),
                                    new SqlParameter("@EducationId", model.EducationId),
                                    new SqlParameter("@PoliticalAffiliationId", model.PoliticalAffiliationId),
                                    new SqlParameter("@TrailDaysId", model.TrailDaysId),
                                    new SqlParameter("@Nationalities", model.Nationalities),
                                    new SqlParameter("@MaritalStatusId", model.MaritalStatusId),
                                    new SqlParameter("@Email", model.Email),
                                    new SqlParameter("@TelePhone", model.TelePhone),
                                    new SqlParameter("@EmergencyContact", model.EmergencyContact),
                                    new SqlParameter("@EmergencyContactPhone", model.EmergencyContactPhone),
                                    new SqlParameter("@Remark", model.Remark),
                                    new SqlParameter("@IsDimission", model.IsDimission));
                }

                #endregion

                #region 添加EmployeeInfo信息

                // 添加一条EmployeeInfo 的信息
                public void InsertEmployeeInfo( EmployeeInfoModel model )
                {
                    SqlHelper.ExecuteNonQuery
                            (
                                    "INSERT INTO EmployeeInfo  (Id,FileNo,Name,DepartmentId,JobTitleId,Gender,Post,IdCard,DateOfBirth,DomicileAddressId,CurrentAddressId,Age,EducationId,PoliticalAffiliationId,TrailDaysId,Nationalities,MaritalStatusId,Email,TelePhone,EmergencyContact,EmergencyContactPhone,Remark,IsDimission  ) values (@Id,@FileNo,@Name,@DepartmentId,@JobTitleId,@Gender,@Post,@IdCard,@DateOfBirth,@DomicileAddressId,@CurrentAddressId,@Age,@EducationId,@PoliticalAffiliationId,@TrailDaysId,@Nationalities,@MaritalStatusId,@Email,@TelePhone,@EmergencyContact,@EmergencyContactPhone,@Remark,@IsDimission)",
                                    new SqlParameter("@Id", model.Id),
                                    new SqlParameter("@FileNo", model.FileNo),
                                    new SqlParameter("@Name", model.Name),
                                    new SqlParameter("@DepartmentId", model.DepartmentId),
                                    new SqlParameter("@JobTitleId", model.JobTitleId),
                                    new SqlParameter("@Gender", model.Gender),
                                    new SqlParameter("@Post", model.Post),
                                    new SqlParameter("@IdCard", model.IdCard),
                                    new SqlParameter("@DateOfBirth", model.DateOfBirth),
                                    new SqlParameter("@DomicileAddressId", model.DomicileAddressId),
                                    new SqlParameter("@CurrentAddressId", model.CurrentAddressId),
                                    new SqlParameter("@Age", model.Age),
                                    new SqlParameter("@EducationId", model.EducationId),
                                    new SqlParameter("@PoliticalAffiliationId", model.PoliticalAffiliationId),
                              
                                    new SqlParameter("@TrailDaysId", model.TrailDaysId),
                                    new SqlParameter("@Nationalities", model.Nationalities),
                                    new SqlParameter("@MaritalStatusId", model.MaritalStatusId),
                                    new SqlParameter("@Email", model.Email),
                                    new SqlParameter("@TelePhone", model.TelePhone),
                                    new SqlParameter("@EmergencyContact", model.EmergencyContact),
                                    new SqlParameter("@EmergencyContactPhone", model.EmergencyContactPhone),
                                    new SqlParameter("@Remark", model.Remark),
                                    new SqlParameter("@IsDimission", model.IsDimission));
                }

                #endregion

                //根据ID来查询EmployeeInfo的信息

                //根据ID修改EmployeeInfo的信息

                #endregion

                #region 扩展方法

                #region 软删除
            /// <summary>
            /// 软删除
            /// </summary>
            /// <param name="id"></param>
                public void DeleteById(Guid id)
                {
                    SqlHelper.ExecuteNonQuery("UPDATE dbo.EmployeeInfo SET IsDimission=1 WHERE Id=@Id", new SqlParameter("@Id", id));
                }
                #endregion

                #region  根据GUID来查询EmployeeInfo中未签订合同的人信息

                public EmployeeInfoModel[] GetEmployeeByGuid()
                {

                    DataTable table = SqlHelper.ExecuteDataTable
                            (
                                    " Select Id,FileNo,DepartmentId,Name,Gender,Post,IdCard,JobTitleId,DateOfBirth,DomicileAddressId,CurrentAddressId,Age,EducationId,PoliticalAffiliationId,ContractId,TrailDaysId,Nationalities,MaritalStatusId,Email,TelePhone,EmergencyContact,EmergencyContactPhone,Remark,IsDimission FROM  EmployeeInfo WHERE IsDimission=0 and ContractId IS null");
                    var operators = new EmployeeInfoModel[table.Rows.Count];
                    for (int i = 0; i < table.Rows.Count; i++)
                        {
                            operators[i] = ToModel(table.Rows[i]);

                        }
                    return operators;

                }

                #endregion

                #region  根据ID修改EmployeeInfo中未签订合同的人状态

                public void UpdatEmployeeInfoByContractId( Guid id, Guid contractId )
                {
                    SqlHelper.ExecuteDataTable
                            (
                                    "UPDATE dbo.EmployeeInfo SET ContractId =@ContractId WHERE ID=@ID",
                                    new SqlParameter("@ContractId", contractId),
                                    new SqlParameter("@ID", id));

                }

                #endregion

                /// <summary>
                ///     数组集合
                /// </summary>
                /// <param name="table"></param>
                /// <returns></returns>
                private EmployeeInfoModel[] ToEmployeeInfoModels( DataTable table )
                {
                    var items = new EmployeeInfoModel[table.Rows.Count];
                    for (int i = 0; i < table.Rows.Count; i++)
                        {
                            items[i] = ToModel(table.Rows[i]);
                        }
                    return items;
                }

                #endregion
         
               
            }
    }